package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.ContractInfoQuoteDevice;
@Transactional(readOnly = true)
public interface ContractInfoQuoteDeviceRepository extends JpaRepository<ContractInfoQuoteDevice, String>{
	@Query(" select ci from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and (ci.superior=?2 and ci.areaName=?3) "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	Page<ContractInfoQuoteDevice> getContractInfos(String key, String superior, String areaName, Pageable pageable);
	
	@Query(" select ci from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.superior = ?2 "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	Page<ContractInfoQuoteDevice> getContractInfos(String key, String superior, Pageable pageable);
	
	@Query(" select ci from ContractInfoQuoteDevice ci where (ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%') "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	Page<ContractInfoQuoteDevice> getContractInfos(String key, Pageable pageable);
	
	@Query(" select ci from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.userId = ?2 and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	Page<ContractInfoQuoteDevice> getContractInfosByUser(String key, String userId, Pageable pageable);
	
	
	@Query(" select count(ci) from ContractInfoQuoteDevice ci where (ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%')"
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoCount(String key);
	
	@Query(" select count(ci) from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.superior = ?2 "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常'")
	long getContractInfoCount(String key, String superior);
	
	@Query(" select count(ci) from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and (ci.superior=?2 and ci.areaName=?3) "
			+ " and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoCount(String key, String superior, String areaName);
	
	@Query(" select count(ci) from ContractInfoQuoteDevice ci where ( ci.supplierCode like '%'+?1+'%' or ci.projectName like '%'+?1+'%' "
			+ "or ci.dutyOfficer like '%'+?1+'%' or ci.orderUnit like '%'+?1+'%' or ci.postUnit like '%'+?1+'%' or ci.contractType like '%'+?1+'%' "
			+ " ) and ci.userId = ?2 and (ci.cstate <> '已排产' or ci.cstate is null) and ci.state = '正常' ")
	long getContractInfoByUserCount(String key, String userId);
}
